import numpy as np
#from pylab import genfromtxt
#import matplotlib.pyplot as plt
#import pylab,matplotlib
#from matplotlib.font_manager import FontProperties
#from matplotlib import rc
#import tkFont
#import gzip,cPickle
import gzip,pickle

#rc('font', **{'family':'serif','serif':['Arial']})
#rc('font', **{'family':'serif','serif':['Palatino']})
#rc('font',**{'family':'sans-serif','sans-serif':['Helvetica']})
#rc('text', usetex=True)

import pymysql

# 打开数据库连接
db = pymysql.connect("172.16.34.71", "root", "HUAyuan123456!!!", "icdsdata")

# 使用cursor()方法获取操作游标
cursor = db.cursor()

bone=[]
x1one=[]
x2one=[]
x3one=[]
x4one=[]
x5one=[]
x6one=[]
x7one=[]
x8one=[]
x9one=[]
x10one=[]
x11one=[]
x12one=[]
x13one=[]

y1one=[]
y2one=[]
y3one=[]
y4one=[]
y5one=[]

# SQL 查询语句
sql = """
SELECT
`工艺检索_设计号数_加字段_SCI_捻度`.`马克隆值`,
`工艺检索_设计号数_加字段_SCI_捻度`.`平均长度`,
`工艺检索_设计号数_加字段_SCI_捻度`.`断裂比强度`,
`工艺检索_设计号数_加字段_SCI_捻度`.`回潮率`,
`工艺检索_设计号数_加字段_SCI_捻度`.`含杂率`,
`工艺检索_设计号数_加字段_SCI_捻度`.`可纺系数`,
`工艺检索_设计号数_加字段_SCI_捻度`.`成熟度`,
`工艺检索_设计号数_加字段_SCI_捻度`.`整齐度`,
`工艺检索_设计号数_加字段_SCI_捻度`.`短纤指数`,
`工艺检索_设计号数_加字段_SCI_捻度`.`反射率`,
`工艺检索_设计号数_加字段_SCI_捻度`.`黄度`,
`工艺检索_设计号数_加字段_SCI_捻度`.`棉结`,

`工艺检索_设计号数_加字段_SCI_捻度`.`工艺`,

`工艺检索_设计号数_加字段_SCI_捻度`.`纱线支数`,
`工艺检索_设计号数_加字段_SCI_捻度`.`修正断裂强度`,

`工艺检索_设计号数_加字段_SCI_捻度`.`实际捻度`,
`工艺检索_设计号数_加字段_SCI_捻度`.`实际捻系数`,
`工艺检索_设计号数_加字段_SCI_捻度`.`加捻效率(%)`
FROM
`工艺检索_设计号数_加字段_SCI_捻度`
"""

# 9.0 < num < 11.0
# 11.0 <num < 15.0
# 15.0 <num < 19.5
# 19.5 <num < 24.0
# 24.0 <num < 29.5
# 29.5 <num < 35.0
# 35.0 <num < 40.0
# 40.0 <num 

try:
    # 执行SQL语句
    cursor.execute(sql)
    # 获取所有记录列表
    resultsori = cursor.fetchall()
    # print(resultsori)
    for row in resultsori:
        bone.append(row[0])  # micronaire # 马克隆值
        x1one.append(row[1])  # uhl # 平均长度
        x2one.append(row[2])  # fiblestr # 断裂比强度
        x3one.append(row[3])  # 回潮率
        x4one.append(row[4])  # 含杂率
        x5one.append(row[5])  # 可纺系数
        x6one.append(row[6])  # 成熟度
        x7one.append(row[7])  # 整齐度
        x8one.append(row[8])  # 短纤指数
        x9one.append(row[9])  # 反射率
        x10one.append(row[10])  # 黄度
        x11one.append(row[11])  # 棉结

        x12one.append(row[12])  # craft # 工艺

        y1one.append(row[13])  # yarn number  # 纱线支数
        y2one.append(row[14])  # yarn strength # 细纱修正断裂强度

        y3one.append(row[15])  # 实际捻度
        y4one.append(row[16])  # 实际捻系数
        y5one.append(row[17])  # 加捻效率
        # 打印结果
#        print("马克隆值=%s,平均长度=%s,工艺=%s,断裂比强度=%s,细纱修正断裂强度=%s,纱线支数=%s" % \
#              (bone, x1one, x3one, x2one, y2one, y1one))
except Expection as e:
    print("Error: unable to fetch data", e )

#print('bone= ', bone)

# 关闭数据库连接
db.close()

#print('test eval= ', eval(resultsori[0][1]))
#print('test eval= ', resultsori[0][0])

numcraft=len(bone)
#print('number of sample = ', numcraft)
#print('max and min yarnnum of sample = ', max(y1one), min(y1one))
#print('max and min yarnstr of sample = ', max(y2one), min(y2one))

bone=np.array([eval(bone[i] ) for i in range(numcraft)])
x1one=np.array([eval(x1one[i]) for i in range(numcraft)])
x2one=np.array([eval(x2one[i]) for i in range(numcraft)])
x3one=np.array([eval(x3one[i]) for i in range(numcraft)])
x4one=np.array([eval(x4one[i]) for i in range(numcraft)])
x5one=np.array([eval(x5one[i]) for i in range(numcraft)])
x6one=np.array([eval(x6one[i]) for i in range(numcraft)])
x7one=np.array([eval(x7one[i]) for i in range(numcraft)])
x8one=np.array([eval(x8one[i]) for i in range(numcraft)])
x9one=np.array([eval(x9one[i]) for i in range(numcraft)])
x10one=np.array([eval(x10one[i]) for i in range(numcraft)])
x11one=np.array([eval(x11one[i]) for i in range(numcraft)])

x12one=np.array([eval(x12one[i]) for i in range(numcraft)])

#print('y2one string= ', y2one)
y1one=np.array([eval(y1one[i]) for i in range(numcraft)])
y2one=np.array([eval(y2one[i]) for i in range(numcraft)])
#print('y2one value= ', y2one)

y3one=np.array([eval(y3one[i]) for i in range(numcraft)])
y4one=np.array([eval(y4one[i]) for i in range(numcraft)])
y5one=np.array([eval(y5one[i]) for i in range(numcraft)])



bonen =(bone-min(bone)+0.02)/(max(bone)-min(bone)+0.05)
x1onen=(x1one-min(x1one)+0.02)/(max(x1one)-min(x1one)+0.05)
x2onen=(x2one-min(x2one)+0.02)/(max(x2one)-min(x2one)+0.05)
x3onen=(x3one-min(x3one)+0.02)/(max(x3one)-min(x3one)+0.05)
x4onen=(x4one-min(x4one)+0.02)/(max(x4one)-min(x4one)+0.05)
x5onen=(x5one-min(x5one)+0.02)/(max(x5one)-min(x5one)+0.05)
x6onen=(x6one-min(x6one)+0.02)/(max(x6one)-min(x6one)+0.05)
x7onen=(x7one-min(x7one)+0.02)/(max(x7one)-min(x7one)+0.05)
x8onen=(x8one-min(x8one)+0.02)/(max(x8one)-min(x8one)+0.05)
x9onen=(x9one-min(x9one)+0.02)/(max(x9one)-min(x9one)+0.05)
x10onen=(x10one-min(x10one)+0.02)/(max(x10one)-min(x10one)+0.05)
x11onen=(x11one-min(x11one)+0.02)/(max(x11one)-min(x11one)+0.05)

x12onen=x12one

y1onen=(y1one-min(y1one)+0.005)/(max(y1one)-min(y1one)+0.01)
y2onen=(y2one-min(y2one)+0.005)/(max(y2one)-min(y2one)+0.01)

y3onen=(y3one-min(y3one)+0.005)/(max(y3one)-min(y3one)+0.01)
y4onen=(y4one-min(y4one)+0.005)/(max(y4one)-min(y4one)+0.01)
y5onen=(y5one-min(y5one)+0.005)/(max(y5one)-min(y5one)+0.01)

yalln=list(zip(y1onen,y2onen))#,y3onen,y4onen,y5onen))
#yalln=zip(yonen,y3onen)
#print('yalln=',yalln[0])

nlinear=2
xall=list(zip(bonen,x1onen,x2onen,x3onen,x4onen,x5onen,x6onen,x7onen,x8onen,x9onen,x10onen,x11onen,x12onen, y3onen, y4onen, y5onen))
#xall=list(zip(bonen,x1onen,x2onen,x4onen,x5onen,x6onen,x7onen,x8onen,x9onen,x10onen,x11onen,x12onen,x3onen, y3onen, y4onen, y5onen))
#xall=zip(bonen,x1onen,x2onen)

datatr=[]
xnum=len(xall)

xallpoly=np.zeros(xnum)
for i in range(numcraft):
	datatr.append([xall[i]])
	for j in range(2,nlinear):
		for k in range(xnum):
			xallpoly[k]=xall[i][k]**j
		datatr[i].append((xallpoly[0],xallpoly[1],xallpoly[2],xallpoly[3]))
		#datatr[i].append(xall[i]**j)

datatr=[datatr]
datatr.append(yalln)
#print('datatr=',len(datatr[1]))
datatrsh=np.array(datatr[0])
datatrsh=datatrsh.reshape([len(datatrsh), len(datatrsh[0][0])])
datatrshy=np.array(datatr[1])
#print('datatr.shape=', datatrsh.shape)
#print('datatr.shape=', datatrshy.shape)

trd=[datatr]
trd.append(datatr)
trd.append(datatr)

outd=1
if outd==2:
   #print trd[0][0][0:size]
   filename=open('craft-all-5-nn.pkl','wb')
   #cPickle.dump(datatr,filename,protocol=cPickle.HIGHEST_PROTOCOL)
   pickle.dump(trd,filename,protocol=pickle.HIGHEST_PROTOCOL)
   filename.close()

#plt.plot(xx2, yy2[0,:], 'b-',linewidth=2)
#plt.plot(xx, yy, 'bD-',linewidth=2)
#plt.plot(xev, yev, 'gD-',linewidth=2)
#plt.plot(xte, yte, 'rD-',linewidth=2)

#plt.xlabel(r'Fiber Length(mm)', fontsize=20)
#plt.ylabel(r'No. of Textiles', fontsize=20)
#matplotlib.rcParams.update({'font.size': 20})
#plt.savefig('data.pdf',bbox_inches='tight')
#plt.show()

